Practical Reverse Engineering: Deconstructing Query Plans (EXPLAIN QUERY PLAN)

The Anatomy of EXPLAIN QUERY PLAN

In database reverse engineering, confirming that a complex SQL query yields correct results is merely the baseline. The true objective is reverse engineering the underlying query execution planner to dissect how the engine traverses structural data blocks.

===================================================================================
                   EXPLAIN QUERY PLAN REVERSE ENGINE TOPOLOGY
===================================================================================

  [ EXPLAIN QUERY PLAN SELECT * FROM movies WHERE title = 'Inception'; ]
  
  ├── Case A (Unindexed) : SCAN TABLE movies (Worst Case O(N))
  └── Case B (Indexed)   : SEARCH TABLE movies USING COVERING INDEX (Optimal O(log N))

===================================================================================

Architectural Dichotomy of Engine Scans

• SCAN TABLE (Sequential Scan): Indicates that the engine will perform a raw, unindexed read across every allocated physical page of the target table. If a table houses 10 million rows, the engine executes 10 million distinct comparisons.
• SEARCH TABLE USING INDEX (Index Scan): Highlights that the engine bypasses raw table reads entirely, steering straight into a balanced B-Tree index to isolate the target row pointer in O(log N) logarithmic hops.
• COVERING INDEX: The absolute apex of query performance. Occurs when the B-Tree index structure explicitly stores every attribute requested within the SELECT clause, allowing the engine to return results directly from the index blocks without ever fetching the primary table page.